
******************************************************************************************************
** BUILD A TRANSACTION FILE
******************************************************************************************************



*************************************************************************
** STEP 1: Normalize IJG and Dealscan transaction Data
*************************************************************************

//get borrowers mapped to internal ID
use ../Raw/IJGlobal/transaction_borrower_records, clear
recast str100 Borrower_CompanyName Borrower_ParentCompany
rename Borrower_CompanyName Borrower_CompanyName_IJG
rename Borrower_ParentCompany Borrower_ParentCompany_IJG
duplicates drop
joinby Borrower_CompanyName_IJG using ../Intermediate/ijglobal_borrowerid_map
keep TransactionId borrower_id 
duplicates drop
merge m:1 borrower_id using ../Intermediate/borrower_file, keepusing(borrower_name) keep(3) nogen
tempfile ij_borrower_map
save `ij_borrower_map'


//STEP 1a: Get IJ Global Debt Tranche Records

use ../Raw/psedo/IJGlobal/transaction_level_data, clear
keep TransactionId TransactionName FinanceType TransactionType TransactionValueLocalCurrenc TransactionCurrency TransactionDebtUSDm 
merge 1:m TransactionId using ../Raw/psedo/IJGlobal/debt_records_tranchelevel, ///
	keepusing(TrancheId DebtInstrumentSecondaryTyp DebtInstrumentTertiaryType TrancheValuem TrancheMaturityStartDate ///
			  TranchePricingType TrancheLoanReferenceRate spread_bps) keep(3) nogen
order TransactionId TrancheId 

// CUT DOWN TO GCEL BORROWERS SAMPLE
joinby TransactionId using `ij_borrower_map', unmatched(both)  //GCEL transaction
keep if _merge == 3
drop _merge

*use facility/tranche level information 
rename TransactionDebtUSDm DealAmount_ijg
rename TrancheValuem TrancheAmount_ijg
rename TrancheMaturityStartDate start_date
gen start_date_m = mofd(start_date)
order borrower_id start_date DealAmount_ijg TrancheAmount_ijg
compress
tempfile ijg_trans 
save `ijg_trans'


//*************************************************************************************


//STEP 1C: Get Dealscan Debt Tranche Records (NEW DEALSCAN DATA) 

use ../Raw/psedo/Dealscan/tr_dealscan_newformat_tranchelevel,clear
rename Borrower_Id CompanyID_DS
joinby CompanyID_DS using ../Intermediate/dealscan_borrowerid_map.dta, unmatched(both)
keep if _merge == 3
drop _merge 
duplicates drop

*rename variables so it's consistent with old format
rename Deal_Amount_Converted DealAmount_ds
rename Tranche_Amount_Converted TrancheAmount_ds
rename Tranche_Active_Date start_date
gen start_date_m = mofd(start_date)
gen obs_ammendment = Tranche_O_A ~= "Origination"

tempfile ds_trans_new 
save `ds_trans_new'
use `ds_trans_new', clear




** STEP 2: Identify Duplicate Transactions Across Datasets


//FINAL VERDICT: MATCH BY BORROWER DAY: KEEP THE IJ GLOBAL DEAL
use `ds_trans_new', clear
joinby borrower_id start_date using `ijg_trans', unmatched(both)		
duplicates report borrower_id TrancheId LPC_Tranche_ID start_date

keep borrower_id TrancheId LPC_Tranche_ID start_date _merge
gen ds_keep = inlist(_merge,1)
gen ijg_keep = inlist(_merge,2,3)
drop _merge

preserve

keep if ds_keep == 1
keep borrower_id LPC_Tranche_ID start_date
duplicates drop 
tempfile dskeep 
save `dskeep'

restore

keep if ijg_keep == 1
keep borrower_id TrancheId 
duplicates drop
tempfile ijgkeep 
save `ijgkeep'


** STEP 3: Combine two datasets accounting for duplicate transactions

// FIRST NEED TO NORMALIZE THE DATA


//Additionally, add in Non-bank Deal level data from Prequin, BDC, and Pitchbook
use "../Raw/psedo/non_bank_loans_bank_ids", clear
gen date_trans = mdy(6, 1, year) //turn year data into mdy date.   
gen debt_type = "Non-Bank Deal"
gen bond_ind = 0
gen obs_ammendment = 0
rename (debt_amt_usdm gcel_parent_id) (amount_trans_usdm borrower_id)
drop lending* year BankID source
tempfile non_bank
save `non_bank'


use `ds_trans_new', clear
merge m:1 borrower_id LPC_Tranche_ID start_date using `dskeep', keep(3) nogen
rename TrancheAmount_ds amount_trans_usdm 
rename All_In_Spread_Drawn_bps spread_bps
rename Tranche_Type loan_type
rename Deal_Purpose deal_purpose
destring amount_trans_usdm,replace
order LPC_Tranche_ID borrower_id start_date amount_trans_usdm deal_purpose loan_type  spread_bps obs_ammendment
keep LPC_Tranche_ID borrower_id start_date amount_trans_usdm deal_purpose loan_type  spread_bps obs_ammendment

tempfile ds_filt_norm 
save `ds_filt_norm'

use `ijg_trans', clear
merge 1:1 borrower_id TrancheId using `ijgkeep', keep(3) nogen
rename TrancheAmount_ijg amount_trans_usdm
rename TransactionName name_trans
rename DebtInstrumentTertiaryType loan_type
rename DebtInstrumentSecondaryTyp debt_inst
rename FinanceType deal_purpose

order TrancheId borrower_id start_date amount_trans_usdm deal_purpose loan_type debt_inst TransactionType spread_bps
keep TrancheId borrower_id start_date amount_trans_usdm deal_purpose loan_type debt_inst TransactionType spread_bps

//now combine IJglobal and dealscan records
append using `ds_filt_norm'
gen source_ds = ~missing(LPC_Tranche_ID)
egen transaction_id = egroup(TrancheId LPC_Tranche_ID start_date), mis
order borrower_id transaction_id LPC_Tranche_ID TrancheId

//clean up differences between dealscan and IJ GLobal
gen project_finance = inlist(deal_purpose,"Project Finance","Proj. finance")
gen refinance_ind = /*refi_ind == "Yes" | */ deal_purpose == "Debt Repay." | TransactionType == "Refinancing"
gen bond_ind = debt_inst == "Bonds" | inlist(loan_type,"Commercial Bond","Fixed-Rate Bond","Municipal Bond","Islamic Bond","Other Bond","Floating Rate Bond")

//get loan type harmonized
gen debt_type = "Bond" if bond_ind == 1
replace debt_type = "Term Loan" if regexm(lower(loan_type),"term")
replace debt_type = "Term Loan" if loan_type == "A Loan"
replace debt_type = "Term Loan" if inlist(loan_type,"Other Loan","Islamic Loan","Mezzanine Debt","Mezzanine Tranche")
replace debt_type = "Revolver" if regexm(lower(loan_type),"revolver") & missing(debt_type)
replace debt_type = "Revolver" if loan_type == "364-Day Facility"
replace debt_type = "Bridge Loan" if regexm(lower(loan_type),"bridge")
replace debt_type = "Revolver" if regexm(lower(loan_type),"facility") & missing(debt_type)
replace debt_type = "Letter of Credit" if regexm(lower(loan_type),"letter") & missing(debt_type)
replace debt_type = "Other" if missing(debt_type)

drop loan_type debt_inst
drop deal_purpose TransactionType /*refi_ind*/ 

order borrower_id transaction_id LPC_Tranche_ID TrancheId start_date amount_trans_usdm project_finance refinance_ind bond_ind debt_type spread_bps source_ds
compress
rename start_date date_trans
//presume IJ Global transactions are not ammendments
replace obs_ammendment = 0 if missing(obs_ammendment)
format date_trans %td
rename transaction_id transaction_loan_id

append using `non_bank' //Adding in non-bank data

//For psedo-code only making transaction_loan_id unique!
duplicates drop transaction_loan_id, force

save ../Intermediate/transaction_clean_loans, replace


******************************************************************************************************
** NOW BUILD A TRANSACTION BY LENDER FILE
******************************************************************************************************

//Add in Non-bank Deal level data from Prequin, BDC, and Pitchbook
use "../Raw/psedo/non_bank_loans_bank_ids", clear
gen BankAllocation_pct = 100
rename (debt_amt_usdm) (amount_trans_lender_usdm)
keep (transaction_loan_id BankID BankAllocation_pct amount_trans_lender_usdm)
tempfile non_bank
save `non_bank'


//Now do dealscan side 
use  ../Raw/Bank_Sample/Dealscan_Bank_BankSample_Link, clear 
keep LoanConnector_Company_ID BankID
duplicates drop
rename LoanConnector_Company_ID Lender_Id
tempfile ds_bank_map 
save `ds_bank_map'

use ../Intermediate/transaction_clean_loans, clear
keep if source_ds == 1
keep transaction_loan_id LPC_Tranche_ID date_trans obs_ammendment
gen Tranche_Active_Date = date_trans
duplicates drop
merge 1:m LPC_Tranche_ID Tranche_Active_Date using ../Raw/psedo/Dealscan/tr_dealscan_newformat_tranchelender, keep(3) nogen
merge m:1 Lender_Id using `ds_bank_map', keep(3) nogen

duplicates tag transaction_loan_id BankID, gen(dups)
sort transaction_loan_id BankID Lender_Id
gcollapse (max) lead_bank = is_lead_bank_v1 (sum) BankAllocation_pct = Lender_Share, by(transaction_loan_id BankID)
replace BankAllocation_pct = . if BankAllocation_pct == 0

tempfile ds_trans_lender 
save `ds_trans_lender'


use  ../Raw/Bank_Sample/IJG_Bank_BankSample_Link, clear
keep IJG_BankName BankID
duplicates drop
rename IJG_BankName Lender_CompanyName
tempfile ijg_bank_map 
save `ijg_bank_map'


use ../Intermediate/transaction_clean_loans, clear
keep if source_ds == 0
keep transaction_loan_id TrancheId date_trans amount_trans_usdm
duplicates drop
merge 1:m TrancheId using "../Raw/psedo/IJGlobal/debt_records_tranche_lender_level.dta", keep(3) nogen
recast str Lender_CompanyName
merge m:1 Lender_CompanyName using `ijg_bank_map', keep(1 3)
duplicates report transaction_loan_id Lender_CompanyName
duplicates report transaction_loan_id BankID
gen lead_bank = 1 
replace lead_bank = 0 if TrancheRole == "Participant" | TrancheRole == "Public Finance Institution" | TrancheRole =="Development Bank" | TrancheRole =="Export Credit Agency" | TrancheRole =="Sub-underwriter" | TrancheRole == "Fund" | TrancheRole =="Paying agent" | TrancheRole == "Security trustee" | TrancheRole =="International Financial Institution"

//collapse allocation and lead arranger by transaction_loan_id bank_id 
gen BankAllocation_pct = 100*LTAccreditedValuem/amount_trans_usdm
replace BankAllocation_pct = . if BankAllocation_pct > 100
gcollapse (max) lead_bank (sum) BankAllocation_pct, by(transaction_loan_id BankID)
replace BankAllocation_pct = . if BankAllocation_pct == 0

tempfile ijg_trans_lender 
save `ijg_trans_lender'

//append the two together
use ../Intermediate/transaction_clean_loans, clear
keep transaction_loan_id amount_trans_usdm
duplicates drop
duplicates report transaction_loan_id
tempfile size
save `size'


use `ds_trans_lender', clear
append using `ijg_trans_lender'

duplicates report transaction_loan_id BankID 
order transaction_loan_id BankID lead_bank BankAllocation_pct

//allocate deal size to banks using available info and pro-rata otherwise 
merge m:1 transaction_loan_id using `size', keep(3) nogen

gen amount_trans_lender_usdm = (BankAllocation_pct/100)*amount_trans_usdm 
egen tot_allocation_frac = sum(BankAllocation_pct/100), by(transaction_loan_id)
replace tot_allocation_frac = . if tot_allocation_frac >= 10
egen nmiss_alloc = sum(missing(BankAllocation_pct)), by(transaction_loan_id)
replace amount_trans_lender_usdm = ((1-tot_allocation_frac)/nmiss_alloc)*amount_trans_usdm if missing(amount_trans_lender_usdm)
drop amount_trans_usdm nmiss_alloc tot_allocation_frac

append using  `non_bank'
merge m:1 BankID using ../Raw/Bank_Sample/BankSample_List, keep(1 3) nogen //Bring in Banknames

save ../Intermediate/transaction_lender_clean_loans, replace




